/****** Object:  Stored Procedure [dbo].tblConstantDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblConstantDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblConstantDelete]
GO

/****** Object:  Stored Procedure [dbo].tblConstantGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblConstantGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblConstantGet]
GO

/****** Object:  Stored Procedure [dbo].tblConstantGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblConstantGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblConstantGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblConstantInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblConstantInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblConstantInsert]
GO

/****** Object:  Stored Procedure [dbo].tblConstantUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblConstantUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblConstantUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblConstantDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblConstantDelete
	@ID int
AS

DELETE FROM [dbo].[tblConstant]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblConstantGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblConstantGet
	@ID int
AS

SELECT
	[ID],
	[ParameterID],
	[StringValue]
FROM
	[dbo].[tblConstant]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblConstantGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblConstantGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblConstant (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblConstant ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblConstant]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblConstant]

SELECT
	[dbo].[tblConstant].[ID],
	[ParameterID],
	[StringValue]
FROM
	#TemptblConstant AS tblTemp JOIN [dbo].[tblConstant] ON
	tblTemp.ID = [dbo].[tblConstant].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblConstant

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblConstantInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblConstantInsert
	@ParameterID int,
	@StringValue varchar(128),
	@ID int output
AS

INSERT INTO [dbo].[tblConstant] 
(
	[ParameterID],
	[StringValue]
) 
VALUES 
(
	@ParameterID,
	@StringValue
)

SELECT @ID = @@IDENTITY

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblConstantUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblConstantUpdate
	@ID int, 
	@ParameterID int, 
	@StringValue varchar(128) 
AS

UPDATE [dbo].[tblConstant] SET
	[ParameterID] = @ParameterID,
	[StringValue] = @StringValue
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblCurrencyInOpicsDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblCurrencyInOpicsDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblCurrencyInOpicsDelete]
GO

/****** Object:  Stored Procedure [dbo].tblCurrencyInOpicsGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblCurrencyInOpicsGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblCurrencyInOpicsGet]
GO

/****** Object:  Stored Procedure [dbo].tblCurrencyInOpicsGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblCurrencyInOpicsGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblCurrencyInOpicsGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblCurrencyInOpicsInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblCurrencyInOpicsInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblCurrencyInOpicsInsert]
GO

/****** Object:  Stored Procedure [dbo].tblCurrencyInOpicsUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblCurrencyInOpicsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblCurrencyInOpicsUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblCurrencyInOpicsDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblCurrencyInOpicsDelete
	@ID int
AS

DELETE FROM [dbo].[tblCurrencyInOpics]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblCurrencyInOpicsGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblCurrencyInOpicsGet
	@ID int
AS

SELECT
	[ID],
	[BuyCurrency],
	[SellCurrency],
	[Status]
FROM
	[dbo].[tblCurrencyInOpics]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblCurrencyInOpicsGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblCurrencyInOpicsGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblCurrencyInOpics (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblCurrencyInOpics ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblCurrencyInOpics]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblCurrencyInOpics]

SELECT
	[dbo].[tblCurrencyInOpics].[ID],
	[BuyCurrency],
	[SellCurrency],
	[Status]
FROM
	#TemptblCurrencyInOpics AS tblTemp JOIN [dbo].[tblCurrencyInOpics] ON
	tblTemp.ID = [dbo].[tblCurrencyInOpics].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblCurrencyInOpics

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblCurrencyInOpicsInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblCurrencyInOpicsInsert
	@BuyCurrency varchar(3),
	@SellCurrency varchar(3),
	@Status int,
	@ID int output
AS

INSERT INTO [dbo].[tblCurrencyInOpics] 
(
	[BuyCurrency],
	[SellCurrency],
	[Status]
) 
VALUES 
(
	@BuyCurrency,
	@SellCurrency,
	@Status
)

SELECT @ID = @@IDENTITY

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblCurrencyInOpicsUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblCurrencyInOpicsUpdate
	@ID int, 
	@BuyCurrency varchar(3), 
	@SellCurrency varchar(3), 
	@Status int 
AS

UPDATE [dbo].[tblCurrencyInOpics] SET
	[BuyCurrency] = @BuyCurrency,
	[SellCurrency] = @SellCurrency,
	[Status] = @Status
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblFileUploadDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblFileUploadDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblFileUploadDelete]
GO

/****** Object:  Stored Procedure [dbo].tblFileUploadGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblFileUploadGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblFileUploadGet]
GO

/****** Object:  Stored Procedure [dbo].tblFileUploadGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblFileUploadGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblFileUploadGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblFileUploadInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblFileUploadInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblFileUploadInsert]
GO

/****** Object:  Stored Procedure [dbo].tblFileUploadUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblFileUploadUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblFileUploadUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblFileUploadDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblFileUploadDelete
	@ID uniqueidentifier
AS

DELETE FROM [dbo].[tblFileUpload]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblFileUploadGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblFileUploadGet
	@ID uniqueidentifier
AS

SELECT
	[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType],
	[Status]
FROM
	[dbo].[tblFileUpload]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblFileUploadGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblFileUploadGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblFileUpload (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID uniqueidentifier	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblFileUpload ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblFileUpload]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblFileUpload]

SELECT
	[dbo].[tblFileUpload].[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType],
	[Status]
FROM
	#TemptblFileUpload AS tblTemp JOIN [dbo].[tblFileUpload] ON
	tblTemp.ID = [dbo].[tblFileUpload].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblFileUpload

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblFileUploadInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblFileUploadInsert
	@FileName varchar(128),
	@UploadDate datetime,
	@UploadBy varchar(7),
	@Path varchar(128),
	@FileType int,
	@Status int,
	@ID uniqueidentifier output
AS

SET @ID = NEWID()

INSERT INTO [dbo].[tblFileUpload] 
(
	[ID],
	[FileName],
	[UploadDate],
	[UploadBy],
	[Path],
	[FileType],
	[Status]
) 
VALUES 
(
	@ID,
	@FileName,
	@UploadDate,
	@UploadBy,
	@Path,
	@FileType,
	@Status
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblFileUploadUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblFileUploadUpdate
	@ID uniqueidentifier, 
	@FileName varchar(128), 
	@UploadDate datetime, 
	@UploadBy varchar(7), 
	@Path varchar(128), 
	@FileType int, 
	@Status int 
AS

UPDATE [dbo].[tblFileUpload] SET
	[FileName] = @FileName,
	[UploadDate] = @UploadDate,
	[UploadBy] = @UploadBy,
	[Path] = @Path,
	[FileType] = @FileType,
	[Status] = @Status
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblImportTransactionDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblImportTransactionDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblImportTransactionDelete]
GO

/****** Object:  Stored Procedure [dbo].tblImportTransactionGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblImportTransactionGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblImportTransactionGet]
GO

/****** Object:  Stored Procedure [dbo].tblImportTransactionGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblImportTransactionGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblImportTransactionGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblImportTransactionInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblImportTransactionInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblImportTransactionInsert]
GO

/****** Object:  Stored Procedure [dbo].tblImportTransactionUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblImportTransactionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblImportTransactionUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblImportTransactionDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblImportTransactionDelete
	@ID uniqueidentifier
AS

DELETE FROM [dbo].[tblImportTransaction]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblImportTransactionGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblImportTransactionGet
	@ID uniqueidentifier
AS

SELECT
	[ID],
	[ValueDate],
	[BuyCurrency],
	[SellCurrency],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[Branch],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5],
	[FromFileID],
	[CreatedBy],
	[CustomerName],
	[CreatedDate],
	[ApprovedDate],
	[ApprovedBy],
	[Channel],
	[BatchNo],
	[CustMasterNo],
	[CusSeg],
	[Status],
	[SlNo],
	[CustCD],
	[TranReferenceNo],
	[Exception],
	[Command]
FROM
	[dbo].[tblImportTransaction]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblImportTransactionGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblImportTransactionGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblImportTransaction (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID uniqueidentifier	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblImportTransaction ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblImportTransaction]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblImportTransaction]

SELECT
	[dbo].[tblImportTransaction].[ID],
	[ValueDate],
	[BuyCurrency],
	[SellCurrency],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[Branch],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5],
	[FromFileID],
	[CreatedBy],
	[CustomerName],
	[CreatedDate],
	[ApprovedDate],
	[ApprovedBy],
	[Channel],
	[BatchNo],
	[CustMasterNo],
	[CusSeg],
	[Status],
	[SlNo],
	[CustCD],
	[TranReferenceNo],
	[Exception],
	[Command]
FROM
	#TemptblImportTransaction AS tblTemp JOIN [dbo].[tblImportTransaction] ON
	tblTemp.ID = [dbo].[tblImportTransaction].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblImportTransaction

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblImportTransactionInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblImportTransactionInsert
	@ValueDate datetime,
	@BuyCurrency varchar(3),
	@SellCurrency varchar(3),
	@CusRate decimal(10, 2),
	@BuyAmount decimal(24, 6),
	@SellAmount decimal(24, 6),
	@Branch varchar(3),
	@Rule1 int,
	@Rule2 int,
	@Rule3 int,
	@Rule4 int,
	@Rule5 int,
	@FromFileID uniqueidentifier,
	@CreatedBy varchar(7),
	@CustomerName varchar(128),
	@CreatedDate datetime,
	@ApprovedDate datetime,
	@ApprovedBy varchar(7),
	@Channel varchar(4),
	@BatchNo varchar(7),
	@CustMasterNo varchar(7),
	@CusSeg varchar(3),
	@Status int,
	@SlNo varchar(20),
	@CustCD varchar(9),
	@TranReferenceNo varchar(20),
	@Exception varchar(1024),
	@Command varchar(128),
	@ID uniqueidentifier output
AS

SET @ID = NEWID()

INSERT INTO [dbo].[tblImportTransaction] 
(
	[ID],
	[ValueDate],
	[BuyCurrency],
	[SellCurrency],
	[CusRate],
	[BuyAmount],
	[SellAmount],
	[Branch],
	[Rule1],
	[Rule2],
	[Rule3],
	[Rule4],
	[Rule5],
	[FromFileID],
	[CreatedBy],
	[CustomerName],
	[CreatedDate],
	[ApprovedDate],
	[ApprovedBy],
	[Channel],
	[BatchNo],
	[CustMasterNo],
	[CusSeg],
	[Status],
	[SlNo],
	[CustCD],
	[TranReferenceNo],
	[Exception],
	[Command]
) 
VALUES 
(
	@ID,
	@ValueDate,
	@BuyCurrency,
	@SellCurrency,
	@CusRate,
	@BuyAmount,
	@SellAmount,
	@Branch,
	@Rule1,
	@Rule2,
	@Rule3,
	@Rule4,
	@Rule5,
	@FromFileID,
	@CreatedBy,
	@CustomerName,
	@CreatedDate,
	@ApprovedDate,
	@ApprovedBy,
	@Channel,
	@BatchNo,
	@CustMasterNo,
	@CusSeg,
	@Status,
	@SlNo,
	@CustCD,
	@TranReferenceNo,
	@Exception,
	@Command
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblImportTransactionUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblImportTransactionUpdate
	@ID uniqueidentifier, 
	@ValueDate datetime, 
	@BuyCurrency varchar(3), 
	@SellCurrency varchar(3), 
	@CusRate decimal(10, 2), 
	@BuyAmount decimal(24, 6), 
	@SellAmount decimal(24, 6), 
	@Branch varchar(3), 
	@Rule1 int, 
	@Rule2 int, 
	@Rule3 int, 
	@Rule4 int, 
	@Rule5 int, 
	@FromFileID uniqueidentifier, 
	@CreatedBy varchar(7), 
	@CustomerName varchar(128), 
	@CreatedDate datetime, 
	@ApprovedDate datetime, 
	@ApprovedBy varchar(7), 
	@Channel varchar(4), 
	@BatchNo varchar(7), 
	@CustMasterNo varchar(7), 
	@CusSeg varchar(3), 
	@Status int, 
	@SlNo varchar(20), 
	@CustCD varchar(9), 
	@TranReferenceNo varchar(20), 
	@Exception varchar(1024), 
	@Command varchar(128) 
AS

UPDATE [dbo].[tblImportTransaction] SET
	[ValueDate] = @ValueDate,
	[BuyCurrency] = @BuyCurrency,
	[SellCurrency] = @SellCurrency,
	[CusRate] = @CusRate,
	[BuyAmount] = @BuyAmount,
	[SellAmount] = @SellAmount,
	[Branch] = @Branch,
	[Rule1] = @Rule1,
	[Rule2] = @Rule2,
	[Rule3] = @Rule3,
	[Rule4] = @Rule4,
	[Rule5] = @Rule5,
	[FromFileID] = @FromFileID,
	[CreatedBy] = @CreatedBy,
	[CustomerName] = @CustomerName,
	[CreatedDate] = @CreatedDate,
	[ApprovedDate] = @ApprovedDate,
	[ApprovedBy] = @ApprovedBy,
	[Channel] = @Channel,
	[BatchNo] = @BatchNo,
	[CustMasterNo] = @CustMasterNo,
	[CusSeg] = @CusSeg,
	[Status] = @Status,
	[SlNo] = @SlNo,
	[CustCD] = @CustCD,
	[TranReferenceNo] = @TranReferenceNo,
	[Exception] = @Exception,
	[Command] = @Command
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblInternalRateDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblInternalRateDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblInternalRateDelete]
GO

/****** Object:  Stored Procedure [dbo].tblInternalRateGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblInternalRateGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblInternalRateGet]
GO

/****** Object:  Stored Procedure [dbo].tblInternalRateGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblInternalRateGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblInternalRateGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblInternalRateInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblInternalRateInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblInternalRateInsert]
GO

/****** Object:  Stored Procedure [dbo].tblInternalRateUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblInternalRateUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblInternalRateUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblInternalRateDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblInternalRateDelete
	@ID uniqueidentifier
AS

DELETE FROM [dbo].[tblInternalRate]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblInternalRateGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblInternalRateGet
	@ID uniqueidentifier
AS

SELECT
	[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy]
FROM
	[dbo].[tblInternalRate]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblInternalRateGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblInternalRateGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblInternalRate (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID uniqueidentifier	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblInternalRate ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblInternalRate]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblInternalRate]

SELECT
	[dbo].[tblInternalRate].[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy]
FROM
	#TemptblInternalRate AS tblTemp JOIN [dbo].[tblInternalRate] ON
	tblTemp.ID = [dbo].[tblInternalRate].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblInternalRate

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblInternalRateInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblInternalRateInsert
	@BuyCCY varchar(3),
	@FloorRate decimal(10, 2),
	@SellCCY varchar(3),
	@CeilingRate decimal(10, 2),
	@InternalRate decimal(10, 2),
	@FromFileID uniqueidentifier,
	@CreatedDate datetime,
	@CreatedBy varchar(7),
	@ID uniqueidentifier output
AS

SET @ID = NEWID()

INSERT INTO [dbo].[tblInternalRate] 
(
	[ID],
	[BuyCCY],
	[FloorRate],
	[SellCCY],
	[CeilingRate],
	[InternalRate],
	[FromFileID],
	[CreatedDate],
	[CreatedBy]
) 
VALUES 
(
	@ID,
	@BuyCCY,
	@FloorRate,
	@SellCCY,
	@CeilingRate,
	@InternalRate,
	@FromFileID,
	@CreatedDate,
	@CreatedBy
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblInternalRateUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblInternalRateUpdate
	@ID uniqueidentifier, 
	@BuyCCY varchar(3), 
	@FloorRate decimal(10, 2), 
	@SellCCY varchar(3), 
	@CeilingRate decimal(10, 2), 
	@InternalRate decimal(10, 2), 
	@FromFileID uniqueidentifier, 
	@CreatedDate datetime, 
	@CreatedBy varchar(7) 
AS

UPDATE [dbo].[tblInternalRate] SET
	[BuyCCY] = @BuyCCY,
	[FloorRate] = @FloorRate,
	[SellCCY] = @SellCCY,
	[CeilingRate] = @CeilingRate,
	[InternalRate] = @InternalRate,
	[FromFileID] = @FromFileID,
	[CreatedDate] = @CreatedDate,
	[CreatedBy] = @CreatedBy
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblOpicCodeDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicCodeDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicCodeDelete]
GO

/****** Object:  Stored Procedure [dbo].tblOpicCodeGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicCodeGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicCodeGet]
GO

/****** Object:  Stored Procedure [dbo].tblOpicCodeGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicCodeGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicCodeGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblOpicCodeInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicCodeInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicCodeInsert]
GO

/****** Object:  Stored Procedure [dbo].tblOpicCodeUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicCodeUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicCodeUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicCodeDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicCodeDelete
	@CustomerName varchar(128),
	@CustomerBranch varchar(3)
AS

DELETE FROM [dbo].[tblOpicCode]
WHERE
	[CustomerName] = @CustomerName
	AND [CustomerBranch] = @CustomerBranch
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicCodeGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicCodeGet
	@CustomerName varchar(128),
	@CustomerBranch varchar(3)
AS

SELECT
	[CustomerName],
	[CustomerBranch],
	[OpicsCode],
	[Portfolio]
FROM
	[dbo].[tblOpicCode]
WHERE
	[CustomerName] = @CustomerName
	AND [CustomerBranch] = @CustomerBranch

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicCodeGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicCodeGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblOpicCode (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	CustomerName varchar(128),	
	CustomerBranch varchar(3)	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblOpicCode ([CustomerName],[CustomerBranch]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [CustomerName],[CustomerBranch] FROM [dbo].[tblOpicCode]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblOpicCode]

SELECT
	[dbo].[tblOpicCode].[CustomerName],
	[dbo].[tblOpicCode].[CustomerBranch],
	[OpicsCode],
	[Portfolio]
FROM
	#TemptblOpicCode AS tblTemp JOIN [dbo].[tblOpicCode] ON
	tblTemp.CustomerName = [dbo].[tblOpicCode].CustomerName  AND 	
	tblTemp.CustomerBranch = [dbo].[tblOpicCode].CustomerBranch 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblOpicCode

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicCodeInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicCodeInsert
	@CustomerName varchar(128),
	@CustomerBranch varchar(3),
	@OpicsCode varchar(20),
	@Portfolio varchar(20)
AS

-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY

INSERT INTO [dbo].[tblOpicCode] (
	[CustomerName],
	[CustomerBranch],
	[OpicsCode],
	[Portfolio]
) VALUES (
	@CustomerName,
	@CustomerBranch,
	@OpicsCode,
	@Portfolio
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicCodeUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicCodeUpdate
	@CustomerName varchar(128), 
	@CustomerBranch varchar(3), 
	@OpicsCode varchar(20), 
	@Portfolio varchar(20) 
AS

UPDATE [dbo].[tblOpicCode] SET
	[OpicsCode] = @OpicsCode,
	[Portfolio] = @Portfolio
WHERE
	[CustomerName] = @CustomerName
	AND [CustomerBranch] = @CustomerBranch

GO
/****** Object:  Stored Procedure [dbo].tblOpicsTransactionDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicsTransactionDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicsTransactionDelete]
GO

/****** Object:  Stored Procedure [dbo].tblOpicsTransactionGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicsTransactionGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicsTransactionGet]
GO

/****** Object:  Stored Procedure [dbo].tblOpicsTransactionGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicsTransactionGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicsTransactionGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblOpicsTransactionInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicsTransactionInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicsTransactionInsert]
GO

/****** Object:  Stored Procedure [dbo].tblOpicsTransactionUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblOpicsTransactionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblOpicsTransactionUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicsTransactionDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicsTransactionDelete
	@ID uniqueidentifier
AS

DELETE FROM [dbo].[tblOpicsTransaction]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicsTransactionGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicsTransactionGet
	@ID uniqueidentifier
AS

SELECT
	[ID],
	[ValueDate],
	[ValueDateInOpics],
	[DealDate],
	[BuyCurrency],
	[SellCurrency],
	[CustomerRate],
	[BuyAmount],
	[SellAmount],
	[InternalRate],
	[Customer],
	[Porfolio],
	[Branch],
	[ImportTransactionID],
	[RunningDate],
	[Status],
	[DealNo]
FROM
	[dbo].[tblOpicsTransaction]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicsTransactionGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicsTransactionGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblOpicsTransaction (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID uniqueidentifier	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblOpicsTransaction ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblOpicsTransaction]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblOpicsTransaction]

SELECT
	[dbo].[tblOpicsTransaction].[ID],
	[ValueDate],
	[ValueDateInOpics],
	[DealDate],
	[BuyCurrency],
	[SellCurrency],
	[CustomerRate],
	[BuyAmount],
	[SellAmount],
	[InternalRate],
	[Customer],
	[Porfolio],
	[Branch],
	[ImportTransactionID],
	[RunningDate],
	[Status],
	[DealNo]
FROM
	#TemptblOpicsTransaction AS tblTemp JOIN [dbo].[tblOpicsTransaction] ON
	tblTemp.ID = [dbo].[tblOpicsTransaction].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblOpicsTransaction

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicsTransactionInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicsTransactionInsert
	@ValueDate datetime,
	@ValueDateInOpics datetime,
	@DealDate datetime,
	@BuyCurrency varchar(3),
	@SellCurrency varchar(3),
	@CustomerRate decimal(10, 2),
	@BuyAmount decimal(24, 6),
	@SellAmount decimal(24, 6),
	@InternalRate decimal(24, 6),
	@Customer varchar(20),
	@Porfolio varchar(20),
	@Branch varchar(2),
	@ImportTransactionID uniqueidentifier,
	@RunningDate datetime,
	@Status int,
	@DealNo varchar(10),
	@ID uniqueidentifier output
AS

SET @ID = NEWID()

INSERT INTO [dbo].[tblOpicsTransaction] 
(
	[ID],
	[ValueDate],
	[ValueDateInOpics],
	[DealDate],
	[BuyCurrency],
	[SellCurrency],
	[CustomerRate],
	[BuyAmount],
	[SellAmount],
	[InternalRate],
	[Customer],
	[Porfolio],
	[Branch],
	[ImportTransactionID],
	[RunningDate],
	[Status],
	[DealNo]
) 
VALUES 
(
	@ID,
	@ValueDate,
	@ValueDateInOpics,
	@DealDate,
	@BuyCurrency,
	@SellCurrency,
	@CustomerRate,
	@BuyAmount,
	@SellAmount,
	@InternalRate,
	@Customer,
	@Porfolio,
	@Branch,
	@ImportTransactionID,
	@RunningDate,
	@Status,
	@DealNo
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblOpicsTransactionUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblOpicsTransactionUpdate
	@ID uniqueidentifier, 
	@ValueDate datetime, 
	@ValueDateInOpics datetime, 
	@DealDate datetime, 
	@BuyCurrency varchar(3), 
	@SellCurrency varchar(3), 
	@CustomerRate decimal(10, 2), 
	@BuyAmount decimal(24, 6), 
	@SellAmount decimal(24, 6), 
	@InternalRate decimal(24, 6), 
	@Customer varchar(20), 
	@Porfolio varchar(20), 
	@Branch varchar(2), 
	@ImportTransactionID uniqueidentifier, 
	@RunningDate datetime, 
	@Status int, 
	@DealNo varchar(10) 
AS

UPDATE [dbo].[tblOpicsTransaction] SET
	[ValueDate] = @ValueDate,
	[ValueDateInOpics] = @ValueDateInOpics,
	[DealDate] = @DealDate,
	[BuyCurrency] = @BuyCurrency,
	[SellCurrency] = @SellCurrency,
	[CustomerRate] = @CustomerRate,
	[BuyAmount] = @BuyAmount,
	[SellAmount] = @SellAmount,
	[InternalRate] = @InternalRate,
	[Customer] = @Customer,
	[Porfolio] = @Porfolio,
	[Branch] = @Branch,
	[ImportTransactionID] = @ImportTransactionID,
	[RunningDate] = @RunningDate,
	[Status] = @Status,
	[DealNo] = @DealNo
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblParameterDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblParameterDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblParameterDelete]
GO

/****** Object:  Stored Procedure [dbo].tblParameterGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblParameterGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblParameterGet]
GO

/****** Object:  Stored Procedure [dbo].tblParameterGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblParameterGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblParameterGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblParameterInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblParameterInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblParameterInsert]
GO

/****** Object:  Stored Procedure [dbo].tblParameterUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblParameterUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblParameterUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblParameterDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblParameterDelete
	@ID int
AS

DELETE FROM [dbo].[tblParameter]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblParameterGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblParameterGet
	@ID int
AS

SELECT
	[ID],
	[ParameterName]
FROM
	[dbo].[tblParameter]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblParameterGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblParameterGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblParameter (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblParameter ([ID]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [ID] FROM [dbo].[tblParameter]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblParameter]

SELECT
	[dbo].[tblParameter].[ID],
	[ParameterName]
FROM
	#TemptblParameter AS tblTemp JOIN [dbo].[tblParameter] ON
	tblTemp.ID = [dbo].[tblParameter].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblParameter

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblParameterInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblParameterInsert
	@ParameterName varchar(128),
	@ID int output
AS

INSERT INTO [dbo].[tblParameter] 
(
	[ParameterName]
) 
VALUES 
(
	@ParameterName
)

SELECT @ID = @@IDENTITY

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblParameterUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblParameterUpdate
	@ID int, 
	@ParameterName varchar(128) 
AS

UPDATE [dbo].[tblParameter] SET
	[ParameterName] = @ParameterName
WHERE
	[ID] = @ID

GO
/****** Object:  Stored Procedure [dbo].tblUserDelete    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblUserDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblUserDelete]
GO

/****** Object:  Stored Procedure [dbo].tblUserGet    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblUserGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblUserGet]
GO

/****** Object:  Stored Procedure [dbo].tblUserGetList    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblUserGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblUserGetList]
GO

	
/****** Object:  Stored Procedure [dbo].tblUserInsert    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblUserInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblUserInsert]
GO

/****** Object:  Stored Procedure [dbo].tblUserUpdate    Script Date: Thursday, October 27, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sptblUserUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sptblUserUpdate]
GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblUserDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblUserDelete
	@Peoplewise varchar(7)
AS

DELETE FROM [dbo].[tblUser]
WHERE
	[Peoplewise] = @Peoplewise
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblUserGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblUserGet
	@Peoplewise varchar(7)
AS

SELECT
	[Peoplewise],
	[UserName],
	[Email],
	[LastLoginDate],
	[Role],
	[Status]
FROM
	[dbo].[tblUser]
WHERE
	[Peoplewise] = @Peoplewise

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblUserGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblUserGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TemptblUser (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	Peoplewise varchar(7)	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TemptblUser ([Peoplewise]) SELECT '
IF @PageSize > 0
   SET @sql = @sql + ' TOP ' + CAST(@Top as nvarchar)
SET @sql = @sql + ' [Peoplewise] FROM [dbo].[tblUser]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [tblUser]

SELECT
	[dbo].[tblUser].[Peoplewise],
	[UserName],
	[Email],
	[LastLoginDate],
	[Role],
	[Status]
FROM
	#TemptblUser AS tblTemp JOIN [dbo].[tblUser] ON
	tblTemp.Peoplewise = [dbo].[tblUser].Peoplewise 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TemptblUser

GO

/******************************************************************************
**		File: 
**		Name: [dbo].sptblUserInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblUserInsert
	@Peoplewise varchar(7),
	@UserName varchar(20),
	@Email varchar(50),
	@LastLoginDate datetime,
	@Role int,
	@Status int
AS

-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY

INSERT INTO [dbo].[tblUser] (
	[Peoplewise],
	[UserName],
	[Email],
	[LastLoginDate],
	[Role],
	[Status]
) VALUES (
	@Peoplewise,
	@UserName,
	@Email,
	@LastLoginDate,
	@Role,
	@Status
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].sptblUserUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 27/10/2011 17:41:39
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].sptblUserUpdate
	@Peoplewise varchar(7), 
	@UserName varchar(20), 
	@Email varchar(50), 
	@LastLoginDate datetime, 
	@Role int, 
	@Status int 
AS

UPDATE [dbo].[tblUser] SET
	[UserName] = @UserName,
	[Email] = @Email,
	[LastLoginDate] = @LastLoginDate,
	[Role] = @Role,
	[Status] = @Status
WHERE
	[Peoplewise] = @Peoplewise

GO
